package com.elitel.frame.base.component.quartz;

import com.elitel.common.elasticSearch.ESClient;
import com.elitel.frame.base.component.exportprojectdata.ElasticSerachInfo;
import com.elitel.frame.base.service.BaseSearchService;
import com.elitel.frame.base.service.CfgDbService;
import com.elitel.frame.base.service.CfgExtsearchService;
import com.elitel.frame.base.service.CfgFieldService;
import com.elitel.frame.business.service.DataBaseMetadataService;
import com.elitel.frame.main.entity.BaseSearch;
import com.elitel.frame.main.entity.CfgDb;
import com.elitel.frame.main.entity.CfgExtsearch;
import com.elitel.frame.main.entity.CfgField;
import com.elitel.frame.main.entity.vo.FullTextSearchVo;
import io.netty.util.internal.StringUtil;
import org.quartz.Job;
import org.quartz.JobDetail;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 创建全文检索Job任务，定时更新elasticSearch索引库
 * created by guoyanfei on 2018/3/26
 */
public class SearchServiceQuartz implements Job {
    @Autowired
    private CfgExtsearchService cfgExtsearchService;
    @Autowired
    private CfgDbService cfgDbService;
    @Autowired
    private DataBaseMetadataService dataBaseMetadataService;
    @Autowired
    private BaseSearchService baseSearchService;
    @Autowired
    private ElasticSerachInfo elasticSerachInfo;
    @Autowired
    private CfgFieldService cfgFieldService;

    private String address ="";
    private Integer port = 9300;
    private String clustername = "";
    private String esdbname = "ESDBNAME";
    private String establename = "ESTABLENAME";

    @Override
    public void execute(JobExecutionContext context) throws JobExecutionException {
        address =elasticSerachInfo.address;
        port = elasticSerachInfo.port;
        clustername = elasticSerachInfo.clustername;
        JobDetail jobDetail = context.getJobDetail();
        //全文检索服务ID
        String serviceId = jobDetail.getJobDataMap().getString("serviceId");
        if(serviceId == null || serviceId.equalsIgnoreCase("")){
            return;
        }

        //全文检索服务基本信息
        CfgExtsearch cfgExtsearch = cfgExtsearchService.getSearchByguid(serviceId);
        if(cfgExtsearch == null){
            return;
        }

        //1、设置服务状态为更新索引库
        CfgExtsearch begincfgExtsearch = new CfgExtsearch();
        begincfgExtsearch.setGuid(serviceId);
        begincfgExtsearch.setStatus(2);
        cfgExtsearchService.updateCfgExtsearch(begincfgExtsearch);

        //2、查询全文检索配置信息
        List<FullTextSearchVo> fullTextSearchVos = cfgExtsearchService
                .getBasesearchInfoByserviceId(serviceId);
        if(fullTextSearchVos != null && fullTextSearchVos.size() > 0){
            for (FullTextSearchVo ft: fullTextSearchVos) {
                if(ft.getUpdatetype().equalsIgnoreCase("1")){
                    //增量更新
                    if(ft.getUpdatevalue() == null ||
                            ft.getUpdatevalue().equalsIgnoreCase("")){
                        //更新值为空，全部更新
                        allUpdate(ft,cfgExtsearch);
                    }else{
                        //更新值不为空，增量更新
                        incrementUpdate(ft,cfgExtsearch);
                    }
                }else if(ft.getUpdatetype().equalsIgnoreCase("0")){
                    //全部更新
                    allUpdate(ft,cfgExtsearch);
                }
            }
        }

        //3、设置服务状态为正常
        CfgExtsearch endcfgExtsearch = new CfgExtsearch();
        endcfgExtsearch.setGuid(serviceId);
        endcfgExtsearch.setStatus(1);
        cfgExtsearchService.updateCfgExtsearch(endcfgExtsearch);

        System.out.println("任务调度更新完成-服务ID："+serviceId+",时间："+new Date());


    }

    /**
     * 增量更新elasticsearch索引库
     * created on 2018/03/26
     * @param fullTextSearchVo 实体
     * @param cfgExtsearch 实体
     * @author guoyanfei
     */
    private void incrementUpdate(FullTextSearchVo fullTextSearchVo,
                                 CfgExtsearch cfgExtsearch){
        //数据库连接信息
        CfgDb cfgDb = cfgDbService.selectByKey(fullTextSearchVo.getDbkey());
        //增量更新sql信息
        String tablename = fullTextSearchVo.getTablename();
        String fieldname = "'"+fullTextSearchVo.getDbname()+
                "' "+esdbname+",'"+fullTextSearchVo.getTablename()+"' "+establename+""+
                fullTextSearchVo.getFieldidcode()+
                ","+fullTextSearchVo.getPrimarykeyidcode();
        if(fullTextSearchVo.getUpdatefieldcode() !=null && !fullTextSearchVo.equals("")){
            if(fieldname.indexOf(fullTextSearchVo.getUpdatefieldcode()) ==-1){
                fieldname +=","+fullTextSearchVo.getUpdatefieldcode();
            }
        }
        String strwhere = updateCondition(fullTextSearchVo,cfgDb);
        String order = fullTextSearchVo.getPrimarykeyidcode();

        Integer pageIndex=1;
        Integer pageSize =10000;
        Boolean getdata = true;
        ESClient esClient = new ESClient(address,port,clustername);
        while (getdata){
            List<Map<String,Object>> mapList = getsqlData(cfgDb,tablename,fieldname,
                    strwhere,order,pageIndex,pageSize);
            if(mapList.size() < pageSize){
                getdata = false;
            }
            pageIndex++;

            //更新elasticsearch索引库
            if(mapList != null && mapList.size() > 0){
                Integer count =0;
                for (Map<String,Object> map: mapList) {
                    try {
                        esClient.bulkInsertData(cfgExtsearch.getGuid(),
                                cfgExtsearch.getIndextype(),
                                map);
                        if(!getdata){
                            count++;
                            if(count.equals(mapList.size())){
                                updateValue(fullTextSearchVo,map);
                            }
                        }
                    }catch (Exception e){
                        e.printStackTrace();
                        getdata = false;
                    }

                }
                esClient.flushBulk();
            }
        }
        esClient.closeClient();
    }

    /**
     * 全部更新elasticsearch索引库
     * created on 2018/03/26
     * @param fullTextSearchVo 实体
     * @param cfgExtsearch 实体
     * @author guoyanfei
     */
    private void allUpdate(FullTextSearchVo fullTextSearchVo,
                           CfgExtsearch cfgExtsearch){
        //数据库连接信息
        CfgDb cfgDb = cfgDbService.selectByKey(fullTextSearchVo.getDbkey());
        //增量更新sql信息
        String tablename = fullTextSearchVo.getTablename();
        String fieldname = "'"+fullTextSearchVo.getDbname()+
                "' "+esdbname+",'"+fullTextSearchVo.getTablename()+"' "+establename+","+
                fullTextSearchVo.getFieldidcode()+
                ","+fullTextSearchVo.getPrimarykeyidcode();
        if(fullTextSearchVo.getUpdatefieldcode() !=null && !fullTextSearchVo.equals("")){
            if(fieldname.indexOf(fullTextSearchVo.getUpdatefieldcode()) ==-1){
                fieldname +=","+fullTextSearchVo.getUpdatefieldcode();
            }
        }
        String strwhere = "";
        String order = fullTextSearchVo.getPrimarykeyidcode();
        Integer pageIndex=1;
        Integer pageSize =10000;
        Boolean getData = true;
        Boolean delData = true;
        ESClient esClient = new ESClient(address,port,clustername);

        //1、删除elasticsearch索引库已存在的数据信息
        if(esClient.indexExists(cfgExtsearch.getGuid())){
            String conditions=""+esdbname+":"+fullTextSearchVo.getDbname()+","+establename+":"+fullTextSearchVo.getTablename();
            esClient.delteByQuery(cfgExtsearch.getGuid(),conditions);
        }

        //2、更新elasticsearch索引库
        pageIndex = 1;
        while (getData){
            try {
                //查询更新数据
                List<Map<String,Object>> updatemapList = getsqlData(cfgDb,tablename,fieldname,
                        strwhere,order,pageIndex,pageSize);
                if(updatemapList.size() < pageSize){
                    getData = false;
                }
                pageIndex++;
                //更新elasticsearch索引库
                if(updatemapList != null && updatemapList.size() > 0){
                    Integer count =0;
                    for (Map<String,Object> map: updatemapList) {
                            esClient.bulkInsertData(cfgExtsearch.getGuid(),
                                    cfgExtsearch.getIndextype(),
                                    map);

                            //增量更新值
                            if(fullTextSearchVo.getUpdatetype().equals("1")){
                                if(!getData){
                                    count++;
                                    if(count.equals(updatemapList.size())){
                                        updateValue(fullTextSearchVo,map);
                                    }
                                }
                            }

                    }
                    esClient.flushBulk();
                }

            }catch (Exception e){
                e.printStackTrace();
                getData = false;
                esClient.closeClient();
            }
        }

        esClient.closeClient();
    }

    /**
     * 查询更新数据
     * created on 2018/03/26
     * @param cfgDb 数据库连接信息实体
     * @param tablename 表名称
     * @param fieldname 字段名称集合
     * @param strwhere 查询条件
     * @param order 排序字段
     * @param pageIndex 当前页
     * @param pageSize 页大小
     * @return 数据结果集合
     * @author guoyanfei
     */
    private List<Map<String, Object>> getsqlData(CfgDb cfgDb,String tablename,
                                                 String fieldname,String strwhere,
                                                 String order,Integer pageIndex,
                                                 Integer pageSize){
        Integer BeginIndex=(pageIndex-1)*pageSize+1;
        Integer EndIndex=pageIndex*pageSize;
        List<Map<String,Object>> resultdata = new ArrayList<>();
        StringBuffer sqltxt = new StringBuffer();
        switch (cfgDb.getDbVersion()){
            case "ORACLE":
                sqltxt.append("SELECT "+fieldname+" ");
                sqltxt.append("FROM (");
                sqltxt.append("SELECT A.*, ROWNUM RN FROM (SELECT "+fieldname+" FROM ");
                sqltxt.append(tablename.toString()+" ");
                sqltxt.append(strwhere);
                if(order !=null && !order.equals("")){
                    sqltxt.append(" order by "+order);
                }
                sqltxt.append(")A WHERE ROWNUM <="+EndIndex+") WHERE RN >="+BeginIndex+"");
                break;
            case "MYSQL":
                sqltxt.append(" SELECT ");
                sqltxt.append(fieldname+" ");
                sqltxt.append(" FROM "+tablename+" ");
                sqltxt.append(strwhere);
                if(order !=null && !order.equals("")){
                    sqltxt.append(" order by "+order);
                }
                sqltxt.append(" limit ");
                sqltxt.append(BeginIndex-1+","+pageSize);
                break;
            case "SQLSERVER":
                if(StringUtil.isNullOrEmpty(order)){
                    order = "getdate()";
                }
                sqltxt.append("select "+fieldname+" from( ");
                sqltxt.append("select ROW_NUMBER()over(order by "+order+")RN,");
                sqltxt.append(fieldname+" from "+tablename+" ");
                sqltxt.append(strwhere);
                sqltxt.append(" )a ");
                sqltxt.append(" WHERE RN Between "+BeginIndex+" and "+EndIndex);
                break;
        }
        System.out.println(sqltxt.toString());
        resultdata = dataBaseMetadataService.getSqlResult(cfgDb,sqltxt.toString());
        return resultdata;
    }

    /**
     * 增量更新，更新值
     * created on 2018/03/27
     * @param fullTextSearchVo
     * @author guoyanfei
     */
    private void updateValue(FullTextSearchVo fullTextSearchVo,
                             Map<String,Object> map){
        String updatevalue="";
        for (Map.Entry<String,Object> et:map.entrySet()) {
            if(et.getKey().equals(fullTextSearchVo.getUpdatefieldcode())){
                updatevalue = et.getValue().toString();
            }
        }
        BaseSearch baseSearch = new BaseSearch();
        baseSearch.setGuid(fullTextSearchVo.getGuid());
        baseSearch.setUpdatevalue(updatevalue);
        baseSearchService.updateBaseSearch(baseSearch);
    }

    /**
     * show 增量更新设置更新条件
     * created by guoyanfei on 2018/04/04
     * @param fullTextSearchVo
     * @param cfgDb
     * @return 更新条件
     */
    private String updateCondition(FullTextSearchVo fullTextSearchVo,CfgDb cfgDb){
        String strwhere="";
        switch (cfgDb.getDbVersion()){
            case "ORACLE":
                CfgField cfgField = cfgFieldService.selectFieldByguid(fullTextSearchVo.getUpdatefield());
                if(cfgField !=null){
                    if(cfgField.getDtType().equals("date")){
                        strwhere = " where to_char("+fullTextSearchVo.getUpdatefieldcode()+",'yyyy-mm-dd hh24:mi:ss') > '"+
                                fullTextSearchVo.getUpdatevalue()+"'";
                    }else {
                        strwhere = " where "+fullTextSearchVo.getUpdatefieldcode()+" > '"+
                                fullTextSearchVo.getUpdatevalue()+"'";
                    }
                }
                break;
            case "MYSQL":
                strwhere = " where "+fullTextSearchVo.getUpdatefieldcode()+" > '"+
                        fullTextSearchVo.getUpdatevalue()+"'";
                break;
            case "SQLSERVER":
                strwhere = " where "+fullTextSearchVo.getUpdatefieldcode()+" > '"+
                        fullTextSearchVo.getUpdatevalue()+"'";
                break;

        }
        return strwhere;
    }

}
